/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.uq.comp7705.util;

import edu.uq.comp7705.entity.Drink;
import edu.uq.comp7705.entity.Person;
import edu.uq.comp7705.entity.Session;
import edu.uq.comp7705.entity.SessionDrink;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;

/**
 *
 * @author javierpanchi
 */
public class UtilDB {

    EntityManagerFactory emf = Persistence.createEntityManagerFactory("DrinkmeterPU");

    public Person savePerson(Person person) {
        EntityManager em = emf.createEntityManager();
        try {
            em.getTransaction().begin();
            em.persist(person);
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "exception caught", e);
            em.getTransaction().rollback();
        } finally {
            em.close();
        }
        return person;
    }

    public Integer saveSession(Session session) {
        EntityManager em = emf.createEntityManager();
        try {
            em.getTransaction().begin();
            em.persist(session);
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "exception caught", e);
            em.getTransaction().rollback();
        } finally {
            em.close();
        }
        return session.getId();
    }

    public void saveSessionDrink(SessionDrink sessionDrink) {
        EntityManager em = emf.createEntityManager();
        try {
            em.getTransaction().begin();
            em.persist(sessionDrink);
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "exception caught", e);
            em.getTransaction().rollback();
        } finally {
            em.close();
        }
    }

    public void deleteSession(Session session) {
        EntityManager em = emf.createEntityManager();
        try {
            em.getTransaction().begin();
            Query query = em.createNamedQuery("Session.findByIdPersonIdPlaceId");
            em.remove(query.getSingleResult());
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "exception caught", e);
            em.getTransaction().rollback();
        } finally {
            em.close();
        }
    }

    public void deleteSessionDrink(SessionDrink sessionDrink) {
        EntityManager em = emf.createEntityManager();
        try {
            em.getTransaction().begin();
            em.remove(em.find(SessionDrink.class, sessionDrink.getId()));
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "exception caught", e);
            em.getTransaction().rollback();
        } finally {
            em.close();
        }
    }

    public Person getPersonById(Integer id) {
        EntityManager em = emf.createEntityManager();
        Person p = null;
        try {
            em.getTransaction().begin();
            Query query = em.createNamedQuery("Person.findById");
            query.setParameter("id", id);
            p = (Person) query.getSingleResult();
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "exception caught", e);
            em.getTransaction().rollback();
        } finally {
            em.close();
        }
        return p;
    }

    public Person getPersonByFacebookId(String facebookId) {
        EntityManager em = emf.createEntityManager();
        Person p = null;
        try {
            em.getTransaction().begin();
            Query query = em.createNamedQuery("Person.findByFacebookid");
            query.setParameter("facebookid", facebookId);
            List<Person> people = query.getResultList();
            if (people.isEmpty()) {
                p = null;
            } else {
                p = (Person) people.get(0);
            }
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "exception caught", e);
            em.getTransaction().rollback();
        } finally {
            em.close();
        }
        return p;
    }

    public Session getSessionByFacebookId(String facebookId) {
        EntityManager em = emf.createEntityManager();
        Session obj = null;
        try {
            em.getTransaction().begin();
            Query query = em.createNamedQuery("Session.findByFacebookid");
            query.setParameter("facebookid", facebookId);
            List<Session> list=(List<Session>)query.getResultList();
            obj = list.get(list.size()-1);
        } catch (Exception e) {
            e.printStackTrace();
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "exception caught", e);
            em.getTransaction().rollback();
        } finally {
            em.close();
        }
        return obj;
    }

    public Session getSessionByPlaceId(Integer placeId) {
        EntityManager em = emf.createEntityManager();
        Session s = null;
        try {
            em.getTransaction().begin();
            Query query = em.createNamedQuery("Session.findByDrkPlaceId");
            query.setParameter("id", placeId);
            s = (Session) query.getResultList().get(query.getResultList().size() - 1);
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "exception caught", e);
            em.getTransaction().rollback();
        } finally {
            em.close();
        }
        return s;
    }

    public Session getSessionById(Integer sessionId) {
        EntityManager em = emf.createEntityManager();
        Session s = null;
        try {
            em.getTransaction().begin();
            Query query = em.createNamedQuery("Session.findById");
            query.setParameter("id", sessionId);
            s = (Session) query.getResultList().get(query.getResultList().size() - 1);
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "exception caught", e);
            em.getTransaction().rollback();
        } finally {
            em.close();
        }
        return s;
    }

    public SessionDrink getSessionDrinkById(Integer sessionDrinkId) {
        EntityManager em = emf.createEntityManager();
        SessionDrink s = null;
        try {
            em.getTransaction().begin();
            Query query = em.createNamedQuery("SessionDrink.findById");
            query.setParameter("id", sessionDrinkId);
            s = (SessionDrink) query.getSingleResult();
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "exception caught", e);
            em.getTransaction().rollback();
        } finally {
            em.close();
        }
        return s;
    }

    public List<Drink> getAllDrinks() {
        EntityManager em = emf.createEntityManager();
        List<Drink> places = null;
        try {
            em.getTransaction().begin();
            Query query = em.createNamedQuery("Drink.findAll");
            places = (List<Drink>) query.getResultList();
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "exception caught", e);
            em.getTransaction().rollback();
        } finally {
            em.close();
        }
        return places;
    }

    public Drink getDrinkById(Integer drinkId) {
        EntityManager em = emf.createEntityManager();
        Drink obj = null;
        try {
            em.getTransaction().begin();
            Query query = em.createNamedQuery("Drink.findById");
            query.setParameter("id", drinkId);
            obj = (Drink) query.getSingleResult();
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "exception caught", e);
            em.getTransaction().rollback();
        } finally {
            em.close();
        }
        return obj;
    }

    public SessionDrink updateDrinkSession(Integer idDrink, Integer idSession) {
        EntityManager em = emf.createEntityManager();
        SessionDrink obj = null;
        try {
            em.getTransaction().begin();
            Query query = em.createNamedQuery("SessionDrink.findByDrinkSession");
            query.setParameter("drink", idDrink);
            query.setParameter("session", idSession);

            if (query.getFirstResult() == 0) {
                obj = new SessionDrink();
                obj.setDrkDrinksId(getDrinkById(idDrink));
                obj.setDrkSessionId(getSessionById(idSession));
                obj.setQuantity(1);
                em.persist(obj);
            } else {
                obj = (SessionDrink) query.getSingleResult();
                obj.setQuantity(obj.getQuantity() + 1);
                em.merge(obj);
            }
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "exception caught", e);
            em.getTransaction().rollback();
        } finally {
            em.close();
        }
        return obj;
    }

    public List<SessionDrink> getListSessionDrinkBySessionId(Integer idSession) {
        EntityManager em = emf.createEntityManager();
        List<SessionDrink> obj = new ArrayList<SessionDrink>();
        try {
            em.getTransaction().begin();
            Query query = em.createNamedQuery("SessionDrink.findBySession");
            query.setParameter("session", idSession);
            obj = (List<SessionDrink>) query.getResultList();
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "exception caught", e);
            em.getTransaction().rollback();
        } finally {
            em.close();
        }
        return obj;
    }

    public List<Session> getSessionByPersonId(Integer idPerson) {
        EntityManager em = emf.createEntityManager();
        List<Session> obj = new ArrayList<Session>();
        try {
            em.getTransaction().begin();
            Query query = em.createNamedQuery("Session.findByPerson");
            query.setParameter("idPerson", idPerson);
            obj = (List<Session>) query.getResultList();
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "exception caught", e);
            em.getTransaction().rollback();
        } finally {
            em.close();
        }
        return obj;
    }

    public void deleteDrinkSessionByDrinkIdSessionId(Integer idDrink, Integer idSession) {
        EntityManager em = emf.createEntityManager();
        try {
            em.getTransaction().begin();
            Query query = em.createNamedQuery("SessionDrink.findByDrinkIdSessionId");
            query.setParameter("idDrink", idDrink);
            query.setParameter("idSession", idSession);
            for (SessionDrink sessionDrink : (List<SessionDrink>) query.getResultList()) {
                em.remove(sessionDrink);
            }
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "exception caught", e);
            em.getTransaction().rollback();
        } finally {
            em.close();
        }
    }

    public void closeSession(String facebookid) {
        EntityManager em = emf.createEntityManager();
        try {
            em.getTransaction().begin();
            Query query = em.createNamedQuery("Session.findByFacebookid");
            query.setParameter("facebookid", facebookid);
            Session session = (Session) query.getSingleResult();
            session.setClosed(1);
            em.merge(session);
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "exception caught", e);
            em.getTransaction().rollback();
        } finally {
            em.close();
        }
    }
}
